/**
 * Project: topdt-jbpm
 * Copyright 2013  Top Digital Technology  Co. Ltd.
 * All right reserved.
 */
package com.topdt.jbpm.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.thx.common.orm.PaginationDao;
import com.thx.common.util.AppContext;
import com.topdt.jbpm.Constants;
import com.topdt.jbpm.model.Todo;

/**
 * 建立日期 : 2013-4-25 下午12:03:03<br>
 * 作者 : balance<br>
 * 模块 : <br>
 * 描述 : <br>
 * 修改历史: 序号 日期 修改人 修改原因 <br>
 * 1 <br>
 * 2 <br>
 */
@Repository
public class TodoDao extends PaginationDao<Todo> {

	public List<Todo> getAll(String userId, String todoTitle) {
		String comId = AppContext.getCurrentComId();
		String sql = "select * from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running' "
				+ " and w.app_id in (select a.app_id from wf_application a where (a.app_attr='1' or a.app_attr='3' or a.app_attr='5') and a.is_valid='1' ))";
		if (todoTitle != null) {
			sql += " and t.todo_title like '%" + todoTitle + "%'";
		}
		sql += " order by t.arrive_time desc";
		List<Object> todos = this.findPageListBySql(Todo.class, false, sql, userId, comId);
		return convertTodoList(todos);
	}

	/**
	 * 重载getAll方法，根据appId查找待办
	 *
	 * @author 冯晋首
	 * @date 2015年12月22日 上午9:03:29
	 * @param userId
	 * @param todoTitle
	 * @param appId
	 * @return
	 */
	public List<Todo> getAll(String userId, String todoTitle, String appId) {
		String comId = AppContext.getCurrentComId();
		String sql = "select * from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running' ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			if (appId.indexOf(",") == -1) {
				sql += " and w.app_id = '" + appId + "') ";
			} else {
				sql += " and w.app_id in (" + appId + "))";
			}
		} else {
			sql += " and w.app_id in (select a.app_id from wf_application a where (a.app_attr='1' or a.app_attr='3' or a.app_attr='5') and a.is_valid='1' ))";
		}
		if (todoTitle != null) {
			sql += " and t.todo_title like '%" + todoTitle + "%'";
		}
		sql += " order by t.arrive_time desc";
		List<Object> todos = this.findPageListBySql(Todo.class, false, sql, userId, comId);
		return convertTodoList(todos);
	}

	/**
	 * 分页获取用户所有代办
	 *
	 * @author 刘杰
	 * @date 2015年12月28日 下午2:21:46
	 * @param userId
	 * @param todoTitle
	 * @return
	 */
	public List<Todo> getAllTodo(String userId, String todoTitle) {
		String comId = AppContext.getCurrentComId();
		String sql = "select * from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running') ";
		if (todoTitle != null) {
			sql += " and t.todo_title like '%" + todoTitle + "%'";
		}
		sql += " order by t.arrive_time desc";
		List<Object> todos = this.findPageListBySql(Todo.class, false, sql, userId, comId);
		return convertTodoList(todos);
	}

	/**
	 * 得到待办总数
	 *
	 * @author 刘杰
	 * @date 2016年1月20日 下午8:32:36
	 * @param userId
	 * @return
	 */
	public int getTodoTotalNum(String userId) {
		String comId = AppContext.getCurrentComId();
		String sql = "select count(*) from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running') ";
		sql += " order by t.arrive_time desc";
		Object o = createSqlQuery(sql, userId, comId).uniqueResult();
		if (o != null) {
			return Integer.parseInt(o.toString());
		}
		return 0;
	}

	/**
	 * 客户端接口根据参数取得公文待办信息
	 *
	 * @author zhangyong
	 * @date 2015年12月23日 上午11:30:16
	 * @param personnelId
	 * @param appId
	 * @param start
	 * @param max
	 * @param todoTitle
	 * @return
	 */
	public List<Todo> getTodoByAppIdOfDodument(String personnelId, String appId, int start, int max, String todoTitle) {
		String comId = AppContext.getCurrentComId();
		String sql = "select  * from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running' ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			// fengjinshou 20160217
			if (appId.indexOf(",") == -1) {
				sql += " and w.app_id = '" + appId + "') ";
			} else {
				sql += " and w.app_id in (" + appId + ")) ";
			}
		} else {
			sql += " and w.app_id in (select a.app_id from wf_application a where (a.app_attr='1' or a.app_attr='3' or a.app_attr='5') and a.is_valid='1' ))";
		}
		if (todoTitle != null) {
			sql += " and t.todo_title like '%" + todoTitle + "%'";
		}
		sql += " order by t.arrive_time desc";
		List<Todo> todos = createSqlQuery(sql, personnelId, comId).addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
		return todos;

	}

	private List<Todo> convertTodoList(List<Object> todos) {
		List<Todo> todoList = new ArrayList<Todo>();
		for (int i = 0; i < todos.size(); i++) {
			Object todo = todos.get(i);
			todoList.add((Todo) todo);
		}
		return todoList;
	}

	@SuppressWarnings("unchecked")
	public List<Todo> getTodosByState(String userId, String state) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "from Todo where " + condition + " toUserId=? and state=?";
		List<Todo> todos = this.createQuery(hql, userId, state).list();
		return todos;
	}

	public void deleteOthers(String workId, String todoId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "delete from Todo where " + condition + " workId=? and todoId<>?";
		this.createQuery(hql, workId, todoId).executeUpdate();
	}

	public void delByWorkId(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "delete from Todo where " + condition + " workId=?";
		this.createQuery(hql, workId).executeUpdate();
	}

	@SuppressWarnings("unchecked")
	public List<Todo> getAllTodos(String userId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "from Todo where " + condition + " toUserId=?";
		List<Todo> todos = this.createQuery(hql, userId).list();
		return todos;
	}

	@SuppressWarnings("unchecked")
	public Todo findTodoByWorkAndUser(String workId, String userId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "from Todo where " + condition + " workId=? and toUserId=?";

		List<Todo> todos = this.createQuery(hql, workId, userId).list();
		if (todos != null && todos.size() > 0)
			return todos.get(0);
		return null;
	}

	@SuppressWarnings("unchecked")
	public List<Todo> getTodosByWorkId(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "from Todo where " + condition + " workId=?";
		List<Todo> todos = this.createQuery(hql, workId).list();
		return todos;
	}

	/**
	 * 查询待办事宜列表 建立日期 : June 21, 2013 <br>
	 * 作者 : 王阿敏<br>
	 *
	 * @param info
	 * @return
	 */
	/*
	 * @SuppressWarnings("unchecked") public List<Todo> getAll(Todo todo, Long
	 * personnelId, int start, int max) { String comId =
	 * AppContext.getCurrentComId(); String condition = ""; if (comId != null &&
	 * !"".equals(comId)) condition = "todo.company_id='" + comId + "' and ";
	 *
	 * String todoTitle = ""; if (todo != null && !"".equals(todo) &&
	 * todo.getTodoTitle() != null && !"".equals(todo.getTodoTitle())) {
	 * todoTitle = " todo.todo_title like '%" + todo.getTodoTitle() + "%' and ";
	 * }
	 *
	 * // String sql = "select * from wf_todo todo " + " where " + condition +
	 * // todoTitle + " todo.to_user_id = ? " // + //
	 * " and not exists (select 1 from wf_work work where todo.work_id= work.work_id"
	 * // + //
	 * " and exists (select 1 from wf_application app where app.is_inherent = 1 ))"
	 * // + " order by todo.arrive_time desc"; String sql =
	 * "select * from wf_todo t where t.to_user_id = ? and t.company_id = ? and t.work_id in("
	 * +
	 * " select w.work_id from wf_work w where w.work_state ='running' )order by t.arrive_time desc"
	 * ; List<Todo> list = createSQLQuery(sql, new Object[] {
	 * String.valueOf(personnelId), comId })
	 * .addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
	 * return list; }
	 */

	/**
	 * 查询待办事宜的总数 建立日期 : June 21, 2013 <br>
	 * 作者 : 王阿敏<br>
	 *
	 * @param info
	 * @return
	 */
	/*
	 * public int queryCount(Todo todo, Long personnelId) { String sql =
	 * "select count(*) " + getSql(todo, personnelId); List list =
	 * createSQLQuery(sql, String.valueOf(personnelId)).list(); if (list.size()
	 * > 0) { return new Integer(list.get(0).toString()); } return 0; }
	 */

	/**
	 * 查询sql语句 建立日期 : Apr 18, 2013 <br>
	 * 作者 : 王阿敏<br>
	 *
	 * @param info
	 * @return
	 */
	public String getSql(Todo todo, Long personnelId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "todo.company_id='" + comId + "' and ";

		String todoTitle = "";
		if (todo != null && !"".equals(todo) && todo.getTodoTitle() != null && !"".equals(todo.getTodoTitle())) {
			todoTitle = " todo.todo_title like '%" + todo.getTodoTitle() + "%' and ";
		}
		String sql = " from wf_todo todo " + " where " + condition + todoTitle + " todo.to_user_id = ? "
				+ " and not exists (select 1 from wf_work work where todo.work_id= work.work_id"
				+ " and exists (select 1 from wf_application app where app.is_inherent = 1 ))"
				+ " order by todo.arrive_time desc";
		return sql;
	}

	public List<String> getActivityNamesByworkId(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "select activityName from Todo where " + condition + " workId=?";
		List<String> activityNames = this.createQuery(hql, workId).list();
		return activityNames;
	}

	/**
	 *
	 * @author wangamin
	 * @date 2013-4-17 上午11:23:40
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Todo> getTodoByUserId(String userId, int num) {
		String comId = AppContext.getCurrentComId();
		String sql = "select  * from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running' )";

		sql += " order by t.arrive_time desc";
		List<Todo> todos = createSqlQuery(sql, userId, comId).addEntity(Todo.class).setFirstResult(0).setMaxResults(num).list();
		return todos;
	}

	/**
	 * 通过appId获取对应的待办工作
	 *
	 * @param personnelId
	 * @param appId
	 * @param start
	 * @param max
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Todo> findByWorkId(Todo todo, Long personnelId, String appId, int start, int max) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "t.company_id='" + comId + "' and ";

		String todoTitle = "";
		if (todo != null && !"".equals(todo) && todo.getTodoTitle() != null && !"".equals(todo.getTodoTitle())) {
			todoTitle = " t.todo_title like '%" + todo.getTodoTitle() + "%' and ";
		}
		String sql = "select t.* from wf_todo t left outer join wf_work work on t.work_id = work.work_id" + " where "
				+ condition + todoTitle + " t.to_user_id=? and work.app_id=? order by t.arrive_time desc";
		List<Todo> list = createSqlQuery(sql, new Object[] { String.valueOf(personnelId), appId }).addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
		return list;
	}

	/**
	 * 通过appId获取待办工作的数量
	 *
	 * @param personnelId
	 * @param appName
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public int queryCountByWorkId(Todo todo, Long personnelId, String appId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "t.company_id='" + comId + "' and ";

		String todoTitle = "";
		if (todo != null && !"".equals(todo) && todo.getTodoTitle() != null && !"".equals(todo.getTodoTitle())) {
			todoTitle = " t.todo_title like '%" + todo.getTodoTitle() + "%' and ";
		}
		String sql = "select count(*) from wf_todo t left outer join wf_work work on t.work_id = work.work_id"
				+ " where " + condition + todoTitle + " t.to_user_id=? and work.app_id=? order by t.arrive_time desc";
		List list = createSqlQuery(sql, new Object[] { String.valueOf(personnelId), appId }).list();
		if (list.size() > 0) {
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;
	}

	/**
	 * 客户端工单管理查询待办
	 *
	 * @author zhangyong
	 * @date 2015年12月23日 下午2:38:19
	 * @param userId
	 * @param appId
	 * @param todoTitle
	 * @param start
	 * @param max
	 * @return
	 */
	public List<Todo> getTodoPageOfTicket(String userId, String appId, String todoTitle, int start, int max) {
		List<Todo> todos;
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (StringUtils.isNotBlank(todoTitle)) {
			condition = "and  t.todo_title like '%" + todoTitle + "%'";
		}
		String sql = " select * from wf_todo t where t.company_id = ? and t.to_user_id = ?  " + condition
				+ " and t.work_id in ( select w.work_id from wf_work w where w.work_state = 'running' ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			// fengjinshou 20160222
			if (appId.indexOf(",") == -1) {
				sql += " and w.app_id = ?) ";
			} else {
				sql += " and w.app_id in (" + appId + ")) ";
			}
		} else {
			sql += " and w.app_id  in (select a.app_id from wf_application a where (a.app_attr='0' or a.app_attr='2' or a.app_attr='4') and a.is_valid='1' ))";
		}

		sql += " order by t.arrive_time desc";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			if (appId.indexOf(",") == -1) {
				todos = this.createSqlQuery(sql, new Object[] { comId, userId, appId }).addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
			} else {
				todos = this.createSqlQuery(sql, new Object[] { comId, userId }).addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
			}
		} else {
			todos = this.createSqlQuery(sql, new Object[] { comId, userId }).addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
		}
		return todos;
	}

	public List<Todo> getTodoPage(String userId, String appId, String todoTitle) {
		List<Object> todos;
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (StringUtils.isNotBlank(todoTitle)) {
			condition = "and  t.todo_title like '%" + todoTitle + "%'";
		}
		String sql = " select * from wf_todo t where t.company_id = ? and t.to_user_id = ?  " + condition
				+ " and t.work_id in ( select w.work_id from wf_work w where w.work_state = 'running' ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			sql += " and w.app_id = ?) ";
		} else {
			sql += " and w.app_id not in (select a.app_id from wf_application a where (a.app_attr='1' or a.app_attr='3' or a.app_attr='5') and a.is_valid='1' ))";
		}

		sql += " order by t.arrive_time desc";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			todos = this.findPageListBySql(Todo.class, false, sql, new Object[] { comId, userId, appId });
		} else {
			todos = this.findPageListBySql(Todo.class, false, sql, new Object[] { comId, userId });
		}
		return this.convertTodoList(todos);
	}

	/**
	 *
	 * 建立日期 : Aug 5, 2013 4:13:45 PM<br>
	 * 作者: 孙耀飞<br>
	 * 模块: 获得代办的Todo集合，分页查询的数量<br>
	 * 描述: <br>
	 * 1 <br>
	 * 2 <br>
	 */
	@SuppressWarnings("unchecked")
	public int getTodoCount(Todo todo, Long personnelId, String appId, String isExpand) {
		String comId = AppContext.getCurrentComId();
		String todoTitle = "";
		if (StringUtils.isNotBlank(todo.getTodoTitle())) {
			todoTitle = "and  t.todo_title like '%" + todo.getTodoTitle() + "%'";
		}
		List list = new ArrayList<Todo>();
		String sql = " select count(*) from wf_todo t where t.company_id = ? and t.to_user_id = ?  " + todoTitle
				+ "and t.work_id in (" + " select w.work_id from wf_work w where  w.work_state = 'running' ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			sql += "and w.app_id = ? ";
		}
		if (StringUtils.isNotBlank(isExpand) && isExpand.equals("yes")) {
			sql += " and w.app_id not in(select app.app_id from wf_application app where app.is_inherent='1' and app.company_id='"
					+ comId + "')";
		}
		sql += ") order by t.arrive_time desc";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			list = createSqlQuery(sql, new Object[] { comId, String.valueOf(personnelId), appId }).list();
		} else {
			list = createSqlQuery(sql, new Object[] { comId, String.valueOf(personnelId) }).list();
		}

		if (list.size() > 0) {
			java.math.BigDecimal count = (java.math.BigDecimal) list.get(0);
			return count.intValue();
		}
		return 0;
	}

	/**
	 * 根据应用ID与用户ID取得待办信息最新总数
	 *
	 * @param appId
	 * @param userId
	 * @return
	 */
	public int getTodoCount(Long uId, String appIds, Date endDate, Date startdate) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "t.company_id='" + comId + "' and ";

		String sql = "select count(1) from wf_todo t left outer join wf_work work on t.work_id = work.work_id"
				+ " where " + condition + " t.to_user_id=? and work.app_id in (" + appIds
				+ ") and  work.work_state = 'running' and t.arrive_time>? " + "  and t.arrive_time<? ";
		List list = createSqlQuery(sql, new Object[] { String.valueOf(uId), startdate, endDate }).list();
		if (list.size() > 0) {
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;
	}

	/**
	 * 根据应用ID与用户ID取得待办信息最新总数
	 *
	 * @param appId
	 * @param userId
	 * @return
	 */
	public int getTodoCount(Long uId, String appIds) {
		String comId = AppContext.getCurrentComId();
		String conditionT = "";
		String conditionW = "";
		if (comId != null && !"".equals(comId)) {
			conditionT = "t.company_id='" + comId + "' and ";
			conditionW = "w.company_id='" + comId + "' and ";
		}

		String sql = "select count(1) from wf_todo t  where " + conditionT + " t.to_user_id=? and "
				+ "exists (select 1 from wf_work w where " + conditionW
				+ " w.work_state = 'running'  and w.app_id in (" + appIds + ") and w.work_id=t.work_id)";

		List list = createSqlQuery(sql, new Object[] { String.valueOf(uId) }).list();
		if (list.size() > 0) {
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;
	}

	@SuppressWarnings("unchecked")
	public List<Todo> findWorkRecordFromTodo(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "todo.companyId='" + comId + "' and ";
		String hql = "from Todo todo where " + condition + " todo.workId = ? order by todo.arriveTime";
		List<Todo> list = createQuery(hql, new Object[] { workId }).list();
		return list;
	}

	public int countExtTodo(String uId) {

		String comId = AppContext.getCurrentComId();
		String conditionT = "";
		String conditionW = "";
		String conditionA = "";
		if (comId != null && !"".equals(comId)) {
			conditionT = "t.company_id='" + comId + "' and ";
			conditionW = "w.company_id='" + comId + "' and ";
			conditionA = "a.company_id='" + comId + "' and ";
		}

		String sql = "select count(1) from wf_todo  t where  " + conditionT
				+ " t.to_user_id=? and exists (select 1 from wf_work w where  " + conditionW
				+ " w.work_state='running' and exists (select 1 from wf_application a where " + conditionA
				+ " a.is_inherent='0' and a.app_id=w.app_id) and w.work_id=t.work_id ) ";

		List list = createSqlQuery(sql, new Object[] { String.valueOf(uId) }).list();
		if (list.size() > 0) {
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;

	}

	public List<Todo> listTodos(String userId, String appId, String isInherent, int start, int max) {

		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "t.company_id='" + comId + "' and w.company_id='" + comId + "'  and ";

		String sql = null;
		if (appId != null && !"".equals(appId.trim()) && !"null".equals(appId)) {
			sql = "select t.todo_id, t.arrive_time, t.from_user,t.activity_name, t.task_id, t.to_user_id, t.work_id, "
					+ "t.state, t.batch, t.seq_num, t.todo_mode, t.dest_activity_name, t.app_name, "
					+ "t.pre_activity_name,t.company_id,w.work_title todo_title,t.is_send from wf_todo t,wf_work w where "
					+ condition + " t.to_user_id=? and w.work_state='running' and w.app_id ='" + appId
					+ "' and t.work_id=w.work_id  order by t.arrive_time desc";
		} else {
			sql = "select t.todo_id, t.arrive_time, t.from_user,t.activity_name, t.task_id, t.to_user_id, t.work_id,t.state, "
					+ "t.batch, t.seq_num, t.todo_mode, t.dest_activity_name, t.app_name, t.pre_activity_name,  t.company_id,"
					+ "w.work_title todo_title,t.is_send from wf_todo t,wf_work w where "
					+ condition
					+ " t.to_user_id=? and w.work_state='running' and "
					+ "exists (select 1 from wf_application a where a.is_inherent='"
					+ isInherent
					+ "' and a.app_id=w.app_id ) " + "and t.work_id=w.work_id order by t.arrive_time desc";
		}

		List<Todo> list = createSqlQuery(sql, userId).addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();

		return list;

	}

	/**
	 * 根据work_id删除wf_todo表中记录
	 *
	 * @author 刘杰
	 * @date May 16, 2014 3:52:18 PM
	 * @param delId
	 */
	public void deleteworks(String delIdsStr) {
		String sql = "delete from wf_todo where work_id    in   (" + delIdsStr + ") ";
		createSqlQuery(sql).executeUpdate();
	}

	/**
	 * 查找有待办工作的人的id
	 *
	 * @author 刘杰
	 * @date Oct 17, 2014 10:15:49 AM
	 * @return
	 */
	public String[][] findUserByTodo() {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "t.company_id='" + comId + "' and w.company_id='" + comId + "'  and ";

		String sql = "select t.to_user_id, " + "count(*) from wf_todo t,wf_work w where " + condition
				+ " w.work_state='running' and t.work_id=w.work_id " + "group by t.to_user_id";

		List<Object[]> list = createSqlQuery(sql).list();
		String[][] users = new String[list.size()][2];
		if (list != null && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				for (int j = 0; j < users.length; j++) {
					users[i][0] = (String) list.get(i)[0];
					users[i][1] = list.get(i)[1].toString();
				}
			}
		}
		return users;
	}

	/**
	 * 查询所有超时的待办工作
	 *
	 * @author LCX
	 * @date 20141212
	 * @param nowTime
	 * @return
	 */
	public List<Todo> getTodoByArriveTime(String nowTime) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId)) {
			condition = "t.company_id='" + comId + "' and w.company_id='" + comId + "'  and ";
		}
		String sql = "select t.todo_id, t.arrive_time, t.from_user,t.activity_name, t.task_id, t.to_user_id, t.work_id, "
				+ "t.state, t.batch, t.seq_num, t.todo_mode, t.dest_activity_name, t.app_name, "
				+ "t.pre_activity_name,  t.company_id,w.work_title todo_title, t.is_send "
				+ "from wf_todo t,wf_work w where "
				+ condition
				+ " "
				+ " t.is_send is null "
				+ "and w.work_state='running' and t.work_id=w.work_id and t.is_send is null and round(to_number(to_date('"
				+ nowTime + "','yyyy-MM-dd hh24:mi:ss')-t.arrive_time)*24)>" + 24 + " order by t.arrive_time desc";
		List<Todo> list = createSqlQuery(sql).addEntity(Todo.class).list();
		return list;
	}

	/**
	 * 得到兄弟待办（同一个工作，同一个活动的其他待办）
	 *
	 * @author zhangyong
	 * @date 2014-11-27 下午05:10:43
	 * @param todoId
	 * @param workId
	 * @param activityName
	 * @return
	 */
	public List<Todo> getOtherTodo(String todoId, String workId, String activityName, long batch) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";
		String hql = "from Todo where " + condition + " workId=? and activityName = ? and todoId <> ?  and batch=?";
		List<Todo> todos = this.createQuery(hql, workId, activityName, todoId, batch).list();
		return todos;
	}

	/**
	 * 更新数据库
	 *
	 * @author LCX
	 * @date 20141209
	 * @param personnelId
	 * @param nowTime
	 * @return
	 */
	public void updateIsSendByWorkId(String todoId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId)) {
			condition = " t.company_id='" + comId + "'  and ";
		}
		String sql = "update wf_todo t set t.is_send = '" + Constants.IS_SEND + "' where " + condition
				+ "t.todo_id = '" + todoId + "' ";
		createSqlQuery(sql).executeUpdate();
	}

	/**
	 * 根据workId查找待办
	 *
	 * @author 刘杰
	 * @date 2016年1月8日 下午8:26:51
	 * @param workId
	 * @return
	 */
	public List<Todo> findTodosByWorkId(String workId) {
		String hql = "from Todo where workId='" + workId + "'";
		List<Todo> todos = this.createQuery(hql).list();
		return todos;
	}

	public Todo findTodoByWorkIdAndActivity(String userId, String workId, String activity) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId)) {
			condition = "companyId='" + comId + "'  and ";
		}
		String hql = "from Todo where "+condition+"toUserId=? and workId=? and activityName=?";
		List<Todo> todos = this.createQuery(hql, userId, workId, activity).list();
		if (todos != null && todos.size() > 0)
			return todos.get(0);
		return null;
	}

}
